import sqlite3
from flask import *
from flask_bootstrap import Bootstrap
import json

port = [5000]
app = Flask(__name__, template_folder='./templates')
bootstrap = Bootstrap(app)


@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close()


@app.route('/', methods=['GET'])
def create():
    conn = sqlite3.connect('mydatabase.db')
    c = conn.cursor()
    c.execute("DROP TABLE IF EXISTS agent")
    c.execute('''CREATE TABLE agent
                    (id INT PRIMARY KEY         NOT NULL,
                    name            char(16)    NOT NULL,
                    x               INT default(0) NOT NULL,
                    y               INT default(0) NOT NULL,
                    crtdate         timestamp default(CURRENT_TIMESTAMP),
                    upddate         timestamp ,
                    state           short default(0) NOT NULL);''')
    conn.commit()
    c = conn.cursor()
    cursor = c.execute("SELECT id,name,x,y,crtdate,upddate,state from agent")
    rv = cursor.fetchall()
    conn.close()
    return render_template('showtable.html', posts=rv)


@app.route('/drop', methods=['GET'])
def drop():
    conn = sqlite3.connect('mydatabase.db')
    c = conn.cursor()
    c.execute("DROP TABLE IF EXISTS agent")
    conn.commit()
    conn.close()
    return json.dumps('Table dropped successfully')


@app.route('/insert', methods=['GET'])
def insert():
    conn = sqlite3.connect('mydatabase.db')
    c = conn.cursor()
    cursor = c.execute("SELECT id FROM agent WHERE id < 5 ")
    if not cursor.fetchall():
        c.execute("INSERT INTO agent(id, name)\
                                      VALUES(1,'Paul')")
        c.execute("INSERT INTO agent(id, name)\
                                      VALUES(2,'Allen')")
        c.execute("INSERT INTO agent(id, name)\
                                      VALUES(3,'Teddy')")
        c.execute("INSERT INTO agent(id, name)\
                                      VALUES(4,'Mark')")
        conn.commit()
        c = conn.cursor()
        cursor = c.execute("SELECT id,name,x,y,crtdate,upddate,state from agent")
        rv = cursor.fetchall()
        conn.close()
        return render_template('showtable.html', posts=rv)
    else:
        return json.dumps('ID conflicts while inserting id=1,2,3,4!')
    


@app.route('/select', methods=['GET'])
def select():
    conn = sqlite3.connect('mydatabase.db')
    c = conn.cursor()
    cursor = c.execute("SELECT id,name,x,y,crtdate,upddate,state from agent")
    rv = cursor.fetchall()
    conn.close()
    return render_template('showtable.html', posts=rv)


@app.route('/update', methods=['GET'])
def update():
    conn = sqlite3.connect('mydatabase.db')
    c = conn.cursor()
    cursor = c.execute("SELECT id FROM agent WHERE id = 1 ")
    if not cursor.fetchall():
        return json.dumps('There is no id=1!')
    else:
        c.execute("UPDATE agent set x = 10, upddate = CURRENT_TIMESTAMP where id = 1")
        conn.commit()
        cursor = c.execute("SELECT id,name,x,y,crtdate,upddate,state from agent")
        rv = cursor.fetchall()
        conn.close()
        return render_template('showtable.html', posts=rv)


@app.route('/delete', methods=['GET'])
def delete():
    conn = sqlite3.connect('mydatabase.db')
    c = conn.cursor()
    cursor = c.execute("SELECT id FROM agent WHERE id = 2 ")
    if not cursor.fetchall():
        return json.dumps('There is no id=2!')
    else:
        c.execute("DELETE from agent where id = 2")
        conn.commit()
        cursor = c.execute("SELECT id,name,x,y,crtdate,upddate,state from agent")
        rv = cursor.fetchall()
        conn.close()
        return render_template('showtable.html', posts=rv)


@app.route('/add', methods=['POST', 'GET'])
def add():
    conn = sqlite3.connect('mydatabase.db')
    c = conn.cursor()
    if request.method == "POST":
        add_id = request.form.get("id")
        add_name = request.form.get("name")
        add_x = request.form.get("x")
        add_y = request.form.get("y")
        add_state = request.form.get("state")
        if add_x == '':
            add_x = 0
        if add_y == '':
            add_y = 0
        if add_state == '':
            add_state = 0
        cursor = c.execute("SELECT id FROM agent WHERE id = ? ", (add_id,))
        if not cursor.fetchall():
            # print(cursor.fetchall())
            c.execute("INSERT INTO agent(id, name, x, y, crtdate, state) VALUES(?,?,?,?,CURRENT_TIMESTAMP,?)",
                      (add_id, add_name, add_x, add_y, add_state))
            conn.commit()
            cursor = c.execute("SELECT id,name,x,y,crtdate,upddate,state from agent")
            rv = cursor.fetchall()
            conn.close()
        else:
            c.execute("UPDATE agent set name =? , x = ?, y = ?, state = ?, upddate = CURRENT_TIMESTAMP where id = ? ",
                      (add_name, add_x, add_y, add_state, add_id))
            conn.commit()
            cursor = c.execute("SELECT id,name,x,y,crtdate,upddate,state from agent")
            rv = cursor.fetchall()
            conn.close()
        return render_template('showtable.html', posts=rv)


@app.route('/singledelete', methods=['POST', 'GET'])
def singledelete():
    conn = sqlite3.connect('mydatabase.db')
    c = conn.cursor()
    if request.method == "POST":
        delete_id = request.form.get("delete")
        print(delete_id)
        c.execute("DELETE FROM agent WHERE id = ? ", (delete_id,))
        conn.commit()
        cursor = c.execute("SELECT id,name,x,y,crtdate,upddate,state from agent")
        rv = cursor.fetchall()
        conn.close()
        return render_template('showtable.html', posts=rv)


if __name__ == '__main__':
    app.run()
